Using DEFAULT, AUTO_INCREMENT, and UNIQUE Constraints in MySQL

Course: MySQL Database Management

Topic: Using DEFAULT, AUTO_INCREMENT, and UNIQUE Constraints

Explore the JustAcademy MySQL Training Course or Register for MySQL Course Demo.

1. Introduction

MySQL provides constraints and column attributes that help maintain accurate, consistent, and reliable database records. DEFAULT, AUTO_INCREMENT, and UNIQUE are commonly used when creating database tables.

• DEFAULT supplies a predefined value when an INSERT statement does not provide a value for a column.
• AUTO_INCREMENT generates numeric identifiers automatically for new records.
• UNIQUE prevents duplicate non-NULL values in a column or combination of columns.

For example, an employee table can generate employee IDs automatically, assign a default employment status, and prevent two employees from having the same email address.

2. Learning Objectives

• Understand the purpose of DEFAULT, AUTO_INCREMENT, and UNIQUE.
• Learn their syntax and implementation in MySQL.
• Create tables using these features.
• Insert records and observe how default and generated values work.
• Prevent duplicate values using UNIQUE constraints.
• Understand how these features differ from primary keys and NOT NULL.
• Learn how to inspect and modify table definitions.
• Apply these concepts in a practical database project.

3. Understanding the DEFAULT Constraint

The DEFAULT clause specifies a value MySQL uses when an INSERT statement omits a column or uses the DEFAULT keyword for that column.

3.1 Syntax

CREATE TABLE table_name (
    column_name data_type DEFAULT default_value
);

3.2 Example: Creating a Table with DEFAULT

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(50) DEFAULT 'General',
    employment_status VARCHAR(20) DEFAULT 'Active',
    salary DECIMAL(10,2) DEFAULT 0.00
);

In this example:

• department defaults to General.
• employment_status defaults to Active.
• salary defaults to 0.00.

3.3 Inserting Data Without Specifying Defaulted Columns

INSERT INTO employees (employee_id, employee_name)
VALUES (101, 'Rahul Sharma');

Because the INSERT statement does not specify the department, employment status, or salary, MySQL uses the defined defaults.

3.4 Expected Result

3.5 Using DEFAULT Explicitly

You can use the DEFAULT keyword in an INSERT statement to request the default value for a column.

INSERT INTO employees (
    employee_id,
    employee_name,
    department,
    employment_status,
    salary
)
VALUES (
    102,
    'Priya Patel',
    DEFAULT,
    DEFAULT,
    DEFAULT
);

This inserts Priya Patel with the configured default department, status, and salary.

3.6 DEFAULT with Different Data Types

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    quantity INT DEFAULT 0,
    price DECIMAL(10,2) DEFAULT 0.00,
    available BOOLEAN DEFAULT TRUE,
    created_date DATE DEFAULT (CURRENT_DATE)
);

This example demonstrates defaults for integer, decimal, boolean-style, and date columns. Expression defaults such as (CURRENT_DATE) depend on the MySQL version and supported expression-default rules.

3.7 DEFAULT and NULL

A default value is not the same as a NOT NULL constraint. If a column allows NULL and an INSERT explicitly supplies NULL, MySQL stores NULL rather than automatically substituting the default in the ordinary case.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    city VARCHAR(50) DEFAULT 'Mumbai'
);

INSERT INTO customers (customer_id, customer_name)
VALUES (1, 'Amit Verma');

INSERT INTO customers (customer_id, customer_name, city)
VALUES (2, 'Neha Shah', NULL);

The first record receives the default city, Mumbai. The second record explicitly supplies NULL, which is permitted because the column is nullable.

3.8 When Should You Use DEFAULT?

• Setting a default status such as Active or Pending.
• Assigning a default quantity of zero.
• Setting a default category or department.
• Providing a default value for flags or preferences.
• Automatically supplying appropriate date or time values when supported.

4. Understanding AUTO_INCREMENT

AUTO_INCREMENT is a MySQL column attribute that automatically generates a numeric value when a new row is inserted without an explicit value for that column. It is commonly used for identifiers such as customer IDs, employee IDs, and order IDs.

4.1 Syntax

column_name INT AUTO_INCREMENT PRIMARY KEY

4.2 Creating a Table with AUTO_INCREMENT

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    email VARCHAR(150)
);

In this table, MySQL automatically generates a value for student_id when an inserted row does not provide one.

4.3 Inserting Records

INSERT INTO students (student_name, email)
VALUES
    ('Rahul Sharma', '[email protected]'),
    ('Priya Patel', '[email protected]'),
    ('Amit Verma', '[email protected]');

MySQL generates identifiers for the inserted rows. In a fresh table with the default starting value, the IDs commonly begin at 1.

4.4 Expected Result

The actual generated values depend on the table's current AUTO_INCREMENT counter and the values already inserted.

4.5 Retrieving the Last Generated ID

Use LAST_INSERT_ID() to retrieve the first automatically generated ID associated with the most recent relevant INSERT statement on the current connection.

INSERT INTO students (student_name, email)
VALUES ('Neha Shah', '[email protected]');

SELECT LAST_INSERT_ID();

This is useful when an application needs the newly generated ID to create related records.

4.6 AUTO_INCREMENT with UNSIGNED

CREATE TABLE orders (
    order_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    order_total DECIMAL(10,2) NOT NULL
);

INT UNSIGNED supports a larger nonnegative range than signed INT. Choose an integer type that can accommodate the expected number of records.

4.7 Starting AUTO_INCREMENT at a Different Value

You can configure a starting sequence value when creating a table or change the next counter value with ALTER TABLE, subject to MySQL's rules.

CREATE TABLE invoices (
    invoice_id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_amount DECIMAL(10,2) NOT NULL
) AUTO_INCREMENT = 1000;

For a compatible existing table, you can also use:

ALTER TABLE invoices AUTO_INCREMENT = 2000;

The counter cannot be set below the maximum existing value in the column. The next generated value is also affected by existing records and the storage engine's behavior.

4.8 Important Rules for AUTO_INCREMENT

• It applies to integer column types.
• A table can have only one AUTO_INCREMENT column.
• The AUTO_INCREMENT column must be indexed.
• It is commonly defined as a primary key.
• Generated values should not be assumed to be gap-free or always consecutive.
• Deleting a record does not guarantee that its generated number will be reused.
• Explicitly inserting an ID can affect the sequence's next value.
• AUTO_INCREMENT cannot be combined with an explicit DEFAULT value on that column.

4.9 When Should You Use AUTO_INCREMENT?

• Customer and employee IDs.
• Order and invoice numbers used as internal identifiers.
• Product IDs.
• Student and registration IDs.
• Any table that needs a simple generated numeric identifier.

5. Understanding the UNIQUE Constraint

The UNIQUE constraint ensures that values in a column or combination of columns are not duplicated. It is useful for enforcing business rules such as preventing duplicate email addresses or usernames.

5.1 Syntax

CREATE TABLE table_name (
    column_name data_type UNIQUE
);

5.2 Example: UNIQUE on Email

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(150) NOT NULL UNIQUE
);

Both usernames and email addresses must be unique among stored rows. The primary key independently ensures that each user ID is unique.

5.3 Inserting Unique Values

INSERT INTO users (username, email)
VALUES ('rahul01', '[email protected]');

INSERT INTO users (username, email)
VALUES ('priya02', '[email protected]');

These statements succeed if their usernames and email addresses do not conflict with existing records.

5.4 What Happens When a Duplicate Is Inserted?

INSERT INTO users (username, email)
VALUES ('rahul01', '[email protected]');

If rahul01 already exists, MySQL rejects the statement with a duplicate-key error because the username has a UNIQUE constraint.

5.5 UNIQUE on Multiple Columns

A composite UNIQUE constraint requires the combination of specified column values to be unique. It does not necessarily require each column to be unique independently.

CREATE TABLE course_enrollments (
    enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE NOT NULL,
    UNIQUE (student_id, course_id)
);

This design prevents the same student from being enrolled in the same course more than once, while allowing different students to enroll in the same course.

5.6 UNIQUE and NULL Values

In MySQL, a UNIQUE index generally permits multiple NULL values in a nullable column because NULL values are not treated as equal for this uniqueness check. If a value is mandatory and unique, combine NOT NULL with UNIQUE.

CREATE TABLE accounts (
    account_id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(150) NOT NULL UNIQUE
);

Here, every account must provide an email address, and duplicate email addresses are rejected.

5.7 Adding a UNIQUE Constraint to an Existing Table

ALTER TABLE customers
ADD CONSTRAINT uq_customers_email UNIQUE (email);

This statement adds a named unique constraint. It will fail if existing data violates the uniqueness rule, so check and resolve duplicates first.

5.8 Removing a UNIQUE Constraint

For a typical MySQL UNIQUE constraint implemented as an index, you can remove it using DROP INDEX.

ALTER TABLE customers
DROP INDEX uq_customers_email;

Use the actual index name from the table definition. Removing a unique index removes the uniqueness enforcement it provided.

5.9 When Should You Use UNIQUE?

• Email addresses that must not be duplicated.
• Usernames and account names.
• Employee codes and registration numbers.
• Product SKUs and reference codes.
• Combinations such as student ID plus course ID.

6. Difference Between DEFAULT, AUTO_INCREMENT, and UNIQUE

7. Difference Between PRIMARY KEY and UNIQUE

8. Using All Three Features Together

DEFAULT, AUTO_INCREMENT, and UNIQUE can be used together to create a table that generates IDs, supplies default values, and prevents duplicate records.

Example: Customer Table

CREATE TABLE customers (
    customer_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    city VARCHAR(50) DEFAULT 'Mumbai',
    account_status VARCHAR(20) NOT NULL DEFAULT 'Active'
);

Explanation:

• customer_id is generated automatically and serves as the primary key.
• customer_name is mandatory.
• email is mandatory and must be unique.
• city defaults to Mumbai when omitted.
• account_status defaults to Active when omitted.

Insert Customer Records

INSERT INTO customers (customer_name, email)
VALUES
    ('Rahul Sharma', '[email protected]'),
    ('Priya Patel', '[email protected]');

SELECT * FROM customers;

The customer IDs are generated automatically. Both customers receive the default city and account status because those columns were omitted from the INSERT statement.

Example of a Duplicate Email

INSERT INTO customers (customer_name, email)
VALUES ('Amit Verma', '[email protected]');

This statement fails if [email protected] already exists, because the email column has a UNIQUE constraint.

9. Practical Project: Employee Management System

In this project, create an employee table that uses all three features and practice inserting, retrieving, and validating data.

Step 1: Create a Database

CREATE DATABASE IF NOT EXISTS employee_management;
USE employee_management;

Step 2: Create the Employees Table

CREATE TABLE employees (
    employee_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    department VARCHAR(50) NOT NULL DEFAULT 'General',
    salary DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    employment_status VARCHAR(20) NOT NULL DEFAULT 'Active'
);

Step 3: Insert Employees

INSERT INTO employees (employee_name, email, salary)
VALUES
    ('Rahul Sharma', '[email protected]', 35000.00),
    ('Priya Patel', '[email protected]', 42000.00),
    ('Amit Verma', '[email protected]', 30000.00);

Step 4: Display All Employees

SELECT * FROM employees;

Step 5: Find Employees in the Default Department

SELECT employee_id, employee_name, department
FROM employees
WHERE department = 'General';

Step 6: Update an Employee's Department

UPDATE employees
SET department = 'IT'
WHERE employee_id = 1;

Step 7: Test the UNIQUE Constraint

INSERT INTO employees (employee_name, email, salary)
VALUES ('Neha Shah', '[email protected]', 38000.00);

If Rahul's email already exists, MySQL rejects this insert because email addresses must be unique.

Step 8: View the Table Definition

SHOW CREATE TABLE employees;

This command displays the table definition, including its primary key, AUTO_INCREMENT attribute, default values, and unique index.

10. Common Errors and Troubleshooting

10.1 Duplicate Entry Error

Cause: An INSERT or UPDATE attempts to create a value that already exists in a UNIQUE index.

Solution: Check whether the value already exists and choose a valid value or update the existing record when appropriate.

SELECT *
FROM employees
WHERE email = '[email protected]';

10.2 Missing Required Value

Cause: A column declared NOT NULL has no supplied value and no usable default.

Solution: Supply a valid value or define a suitable DEFAULT where the business rules allow it.

10.3 AUTO_INCREMENT Column Overflow

Cause: The generated value reaches the maximum value supported by the column's integer type.

Solution: Plan the required identifier range and use a suitable integer type, such as BIGINT for very large tables.

10.4 Adding UNIQUE to Existing Duplicate Data

Cause: Existing records contain duplicate values for the column or column combination being constrained.

Solution: Find and resolve duplicates before adding the unique constraint.

SELECT email, COUNT(*) AS total
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

This query identifies duplicated non-NULL email values. Review the affected rows before changing or deleting any data.

10.5 Assuming AUTO_INCREMENT Values Are Consecutive

Cause: Deleted records, failed inserts, and concurrent operations can result in gaps in generated identifiers.

Solution: Treat generated IDs as identifiers, not as a gap-free sequence for counting records or business numbering.

11. Best Practices

• Use AUTO_INCREMENT for generated numeric identifiers when it fits the application.
• Define the generated ID as a primary key in typical table designs.
• Use UNIQUE for values that must not be duplicated according to business rules.
• Combine NOT NULL and UNIQUE when a unique value is also mandatory.
• Choose DEFAULT values that make sense for the business process.
• Do not use a default value to hide missing information that should be required.
• Do not assume AUTO_INCREMENT values will never have gaps.
• Check existing data before adding a UNIQUE constraint.
• Use meaningful constraint and index names when it improves maintenance.
• Test inserts and updates that should pass as well as those that should fail.
• Inspect table definitions using SHOW CREATE TABLE.
• Use a suitable integer size for the expected number of generated identifiers.

12. Frequently Asked Questions (FAQs)

Q1. What is DEFAULT in MySQL?

DEFAULT specifies the value MySQL uses when an INSERT statement omits a column or explicitly requests its default value.

Q2. What is AUTO_INCREMENT?

AUTO_INCREMENT automatically generates numeric values for new rows when the value is not explicitly supplied.

Q3. What is the purpose of UNIQUE?

UNIQUE prevents duplicate values in a column or combination of columns.

Q4. Can a table have multiple UNIQUE constraints?

Yes. A table can have multiple unique indexes or constraints for different columns or column combinations.

Q5. Can a table have multiple AUTO_INCREMENT columns?

No. MySQL permits only one AUTO_INCREMENT column per table.

Q6. Does DEFAULT apply when NULL is explicitly inserted?

Usually, no. If a nullable column is explicitly assigned NULL, MySQL stores NULL rather than substituting the default. Use the DEFAULT keyword to request the default value.

Q7. Can a UNIQUE column contain NULL?

Yes, if the column allows NULL. MySQL generally permits multiple NULL values in a nullable UNIQUE column.

Q8. Is AUTO_INCREMENT the same as PRIMARY KEY?

No. AUTO_INCREMENT generates values, while PRIMARY KEY uniquely identifies rows and disallows NULL. They are often used together.

Q9. Can I add a UNIQUE constraint after creating a table?

Yes. You can use ALTER TABLE to add a UNIQUE constraint, provided the existing data does not violate it.

Q10. Are AUTO_INCREMENT numbers always consecutive?

No. Gaps can occur because of deleted records, failed statements, and other database operations.

13. Practice Questions

1. What is the purpose of the DEFAULT clause in MySQL?
2. Write SQL to create a table with a default status of Active.
3. What is AUTO_INCREMENT, and how does it work?
4. Write SQL to create a products table with an automatically generated product ID.
5. What is the difference between UNIQUE and PRIMARY KEY?
6. Can a nullable UNIQUE column contain more than one NULL value in MySQL?
7. Write SQL to prevent duplicate email addresses in a users table.
8. How can you find duplicate email addresses in an existing table?
9. How can you inspect a table's DEFAULT and AUTO_INCREMENT definitions?
10. Design an employee table using DEFAULT, AUTO_INCREMENT, and UNIQUE.

14. Quick Revision

• DEFAULT provides a predefined value when a column is omitted or DEFAULT is requested.
• AUTO_INCREMENT generates numeric identifiers for new records.
• UNIQUE prevents duplicate key values.
• A primary key uniquely identifies a row and cannot contain NULL.
• MySQL permits multiple UNIQUE constraints on a table.
• A table can have only one AUTO_INCREMENT column.
• A nullable UNIQUE column can generally contain multiple NULL values.
• AUTO_INCREMENT values may have gaps and should not be treated as gap-free numbering.
• Use NOT NULL alongside UNIQUE when a value is required.
• Use SHOW CREATE TABLE table_name; to inspect table definitions.

15. Official Documentation and Further Learning

Read the official MySQL 8.4 CREATE TABLE Documentation for column definitions, default values, and constraints.

Learn more about Using AUTO_INCREMENT, Data Type Default Values, and PRIMARY KEY and UNIQUE Index Constraints.

Conclusion

DEFAULT, AUTO_INCREMENT, and UNIQUE are important features for designing reliable MySQL tables. DEFAULT provides sensible values, AUTO_INCREMENT generates identifiers, and UNIQUE protects data against duplicate values. Using these features together with primary keys, NOT NULL constraints, and suitable data types helps maintain consistent and dependable database records.

Continue learning through the JustAcademy MySQL Training Course or Register for MySQL Course Demo.